/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package bookingsystem;

import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.net.InetSocketAddress;
import java.net.Socket;
import java.net.SocketAddress;
import java.net.SocketTimeoutException;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Pawel
 */
public class ServerThread implements Runnable {
    private Socket client = null;
    //ArrayList reservations = new ArrayList(); 
    ArrayList<Reservation> reservationsArray;
    private Reservation[] reservations = null;
    ResultSet rset, rowCount, flightrSet = null;
    //Reservation reservation = null;
    ObjectOutputStream oos = null;
    ObjectInputStream ois = null;
    
    public ServerThread(Socket client) {
        this.client = client;
        try {
            oos = new ObjectOutputStream(client.getOutputStream());
            ois = new ObjectInputStream(client.getInputStream());
        } catch (IOException e){
            System.out.println(e);
        }
    }
    
    public void run() {
        try {
            Object inputObj;
            while ((inputObj = ois.readObject()) != null) {
                if (inputObj.equals("areYouAlive"))
                    sendHeartBeat();
                else if (inputObj.equals("readyToReceive"))
                    sendHeartBeat();
                else if (inputObj.equals("reservationsList")) {
                    passReservations();  
                    //saveReservations();
                //updateHelicopter;
                }
                else if (inputObj.equals("updateReservationsList"))
                    updateReservationsList();
                System.out.println(inputObj);

             }
        }
        catch (ClassNotFoundException e){
            System.out.println(e);
        }
        catch (IOException e){
            System.out.println(e);
        }
    }
            
    public void passReservations() {
        try {
            reservationsArray = (ArrayList) ois.readObject();

            Socket centralClient = null;
            int port = 7777;
            centralClient = new Socket();
            SocketAddress sockaddr = new InetSocketAddress("192.168.1.6", port);
            centralClient.connect(sockaddr, 5000);
            
            ClientThread ct;
            ct = new ClientThread(centralClient, oos,  reservationsArray);
            Thread t = new Thread(ct);
            t.start();
            while(true){
                t.sleep(4000);
            }
        }
        catch (SocketTimeoutException ste){
            System.err.println("Server not available... Switching to backup Database...");
            try {
                Socket centralClient = null;
                centralClient = new Socket();
                SocketAddress sockaddr = new InetSocketAddress("127.0.0.1", 8888);
                centralClient.connect(sockaddr, 5000);

                ClientThread ct;
                ct = new ClientThread(centralClient, oos,  reservationsArray);
                Thread t = new Thread(ct);
                t.start();
                while(true){
                    t.sleep(4000);
                }
            }
            catch (IOException e) {
                //System.out.println(e);
                e.printStackTrace();
            }
            catch (InterruptedException ie){
                System.out.println(ie);
            }
        }
        catch (ClassNotFoundException e){
            System.out.println(e);
        }
        catch (InterruptedException ie){
                System.out.println(ie);
        }
        catch (IOException e){
            System.out.println(e);
        }
    }
    
    public void saveReservations() {
        try {
            DBConnection conn = new DBConnection();
            Connection connection = conn.getConnection();
            Statement s = connection.createStatement();
            Statement q = connection.createStatement();
            
            reservationsArray = (ArrayList) ois.readObject();
            
            String reservationId = "";
            String origin = "";
            String destination = "";
            String flightDate = "";
            String flightTime = "";
            String bookingDate = "";
            String custName = "";
            String custLastName = "";
            String status = "";
            int flightNo = 0;

            transferNewReservations(connection);
            //After successful insertion of new reservations from camp
            //Select all reservations with 'Awaiting' status from company database
            rset = s.executeQuery("Select * from reservation where status = 'Awaiting' order by book_date asc");
            while (rset.next()){
                reservationId = rset.getString(1);
                origin = rset.getString(2);
                destination = rset.getString(3);
                flightDate = rset.getString(4);
                flightNo = (int) Integer.parseInt(rset.getString(5));
                bookingDate = rset.getString(6);
                custName = rset.getString(7);
                custLastName = rset.getString(8);
                status = rset.getString(9);
                //Select flight corresponding to reservation to determine seats available
                flightrSet = q.executeQuery("Select * from flight where idflight = '"+flightNo+"'");
                int seatsCapacity = 0;
                int seatsBooked = 0;
                String flightOrigin = "";
                String flightDestination = "";
                String dateOfFlight = "";
                while (flightrSet.next()){
                    seatsCapacity = (int) Integer.parseInt(flightrSet.getString(2));
                    seatsBooked = (int) Integer.parseInt(flightrSet.getString(3));
                    flightOrigin = flightrSet.getString(4);
                    flightDestination = flightrSet.getString(5);
                    dateOfFlight = flightrSet.getString(6);
                }
                if (seatsBooked < seatsCapacity) {
                    confirmReservation(connection, reservationId, flightNo, seatsBooked, "Confirmed");
                }
                else {
                    preBookReservation(connection, reservationId, flightNo);
                }
            }
            
            //Process reservations that answered for 'Reserved' status - either 'OK' or 'Cancelled'
            rset = s.executeQuery("Select * from reservation where status = 'OK' or status='Cancelled' order by book_date asc");
            while (rset.next()){
                reservationId = rset.getString(1);
                flightNo = (int) Integer.parseInt(rset.getString(5));
                status = rset.getString(9);
                if (status.compareTo("OK") == 0) {
                    q.execute("Update reservation Set status='Confirmed' Where idreservation='"+reservationId+"'");
                }
                else { // status == 'Cancelled'
                    q.execute("Update reservation Set status='Cancelled' Where idreservation='"+reservationId+"'");
                    flightrSet = q.executeQuery("Select * from flight where idflight = '"+flightNo+"'");
                    int seatsBooked = 0;
                    while (flightrSet.next()){
                        seatsBooked = (int) Integer.parseInt(flightrSet.getString(3));
                    }
                    seatsBooked--;
                    q.execute("Update flight Set booked_seats='"+seatsBooked+"' Where idflight='"+flightNo+"'");
                }
            }
        }
        catch (ClassNotFoundException e){
            System.out.println(e);
        }
        catch (IOException e){
            System.out.println(e);
        }
        catch (SQLException e) { System.out.println("Conn failed: "+e);// Could not connect to the database
        }
    }
    
    public void transferNewReservations(Connection connection) {
        try {
            Statement s = connection.createStatement();
            String reservationId = "";
            String origin = "";
            String destination = "";
            String flightDate = "";
            String flightTime = "";
            String bookingDate = "";
            String custName = "";
            String custLastName = "";
            String status = "";
            int flightNo = 0;
            //Insert all reservations to the Company DB
            int i = 0;
            while (i<reservationsArray.size()){
                reservationId = reservationsArray.get(i).getReservationId();
                origin = reservationsArray.get(i).getOrigin();
                destination = reservationsArray.get(i).getDestination();
                flightDate = reservationsArray.get(i).getDate();
                flightTime = reservationsArray.get(i).getTime();
                bookingDate = reservationsArray.get(i).getBookingDate();
                flightNo = reservationsArray.get(i).getFlightNo();
                custName = reservationsArray.get(i).getName();
                custLastName = reservationsArray.get(i).getLastName();
                status = reservationsArray.get(i).getStatus();
                boolean queryStatus = s.execute("Insert into reservation values ('"+reservationId+"','"+origin+"','"+destination+"','"+flightDate+" "+flightTime+"','"+flightNo+"','"+bookingDate+"','"+custName+"','"+custLastName+"','"+status+"')");
                i++;
            }
        } catch (SQLException e) { 
            System.out.println("Conn failed: "+e);
        }// Could not connect to the database
    }
    
    public void confirmReservation(Connection connection, String reservationId, int flightNo, int seatsBooked, String status) {
        try {
            Statement s = connection.createStatement();
            seatsBooked++;
            s.execute("Update reservation Set status='"+status+"', flight_no='"+flightNo+"' Where idreservation='"+reservationId+"'");
            s.execute("Update flight Set booked_seats='"+seatsBooked+"' Where idflight='"+flightNo+"'");
            oos.writeObject("ready");
            oos.flush();   
        } catch (IOException e){
            System.out.println(e);
        } catch (SQLException e) { 
            System.out.println("Conn failed: "+e);
        }// Could not connect to the database
    }
    
    public void preBookReservation(Connection connection, String reservationId, int flightNo) {
        try {
            Statement s = connection.createStatement();
            boolean booked = false;
            while (!booked) {
                //search for next available flight
                flightNo = flightNo + 2; // All even flights: camp to city, all odd: city to camp
                flightrSet = s.executeQuery("Select * from flight where idflight = '"+flightNo+"'");
                int seatsCapacity = 0;
                int seatsBooked = 0;
                String flightOrigin = "";
                String flightDestination = "";
                String dateOfFlight = "";
                while (flightrSet.next()){
                    seatsCapacity = (int) Integer.parseInt(flightrSet.getString(2));
                    seatsBooked = (int) Integer.parseInt(flightrSet.getString(3));
                    flightOrigin = flightrSet.getString(4);
                    flightDestination = flightrSet.getString(5);
                    dateOfFlight = flightrSet.getString(6);
                } 
                if (seatsBooked < seatsCapacity) {
                    confirmReservation(connection, reservationId, flightNo, seatsBooked, "Reserved");
                    booked = true;
                }
            }
        } catch (SQLException e) { 
            System.out.println("Conn failed: "+e);
        }// Could not connect to the database
    }
    
    public void sendHeartBeat() {
        try {
            oos.writeObject("ready");
            oos.flush();   
        } catch (IOException e){
            System.out.println(e);
        }
    }
    
//sendFlightsList
    
    public void updateReservationsList() {
        try {
            String lastBookingDate = ois.readObject().toString();
            DBConnection conn = new DBConnection();
            Connection connection = conn.getConnection();
            Statement s = connection.createStatement();
            ArrayList updateReservations = new ArrayList();
            ArrayList updateFlights = new ArrayList();
            rset = s.executeQuery("Select * from reservation where book_date > '"+lastBookingDate+"' order by book_date asc");
            //Reservation[] reservations = new Reservation[numRows];
            while (rset.next()){
                String reservationId = rset.getString(1);
                String origin = rset.getString(2);
                String destination = rset.getString(3);
                String flightDate = rset.getString(4);
                String date = flightDate.substring(0, 10);
                String time = flightDate.substring(11, 16);
                int flightNo = rset.getInt(5);
                String bookingDate = rset.getString(6);
                String custName = rset.getString(7);
                String custLastName = rset.getString(8);
                String status = rset.getString(9);
                updateReservations.add(new Reservation(reservationId,flightNo,custName,custLastName,origin,destination,date,time,bookingDate,status));
            }
            oos.writeObject(updateReservations);
            oos.flush();
            
            String lastFlightNo = ois.readObject().toString();
            rset = s.executeQuery("Select * from flight where idflight > '"+lastFlightNo+"' order by idflight asc");
            
            while (rset.next()){
                int flightId = rset.getInt(1);
                int seatsCapacity = rset.getInt(2);
                int bookedSeats = rset.getInt(3);
                String origin = rset.getString(4);
                String destination = rset.getString(5);
                String flightDate = rset.getString(6);
                String date = flightDate.substring(0, 10);
                String time = flightDate.substring(11, 16);
                updateFlights.add(new Flight(flightId,origin,destination,date,time,seatsCapacity,bookedSeats));
            }
            oos.writeObject(updateFlights);
            oos.flush();
        }
        catch (IOException e){
            System.out.println(e);
        }
        catch (ClassNotFoundException e) {
            System.out.println(e);
        }
        catch (SQLException e) { System.out.println("Conn failed: "+e);// Could not connect to the database
        }
    }
}
